import time
import numpy as np
import pymysql
import pandas as pd
from datetime import datetime
import pymysql.cursors


class MysqlUtils(object):
    def __init__(self, *args):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user="root",
            passwd="root",
            db="scenic2",
            port=3306,
            charset="utf8"
        )
        

    def get_scenic_data(self):
        """获取数据"""
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT 
	    order_id, 
	    age, 
	        user_count, 
	        id_no, 
	        user_name, 
	        phone
        FROM (
        SELECT o.id as order_id, u.id_no, u.user_name, u.phone,
        CASE 
			WHEN LENGTH(u.id_no) = 18 THEN YEAR(NOW()) - CAST(SUBSTR(u.id_no, 7, 4) AS SIGNED) 
			    ELSE NULL 
		    END AS age,
        (SELECT count(*) FROM ticket_order_user_rel WHERE order_id = o.id) as user_count
        FROM ticket_order o join ticket_order_user_rel  u on u.order_id = o.id WHERE u.id_no is not null and u.id_no != '' ) as subquery
        WHERE user_count = 1 and (age < 18 or age >= 60)
        """
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        
        df=pd.DataFrame(ret)
        print(df.head)
        df.to_csv('./outlier/scenic_data.csv')
        
if __name__ =='__main__':
    mu=MysqlUtils()
    mu.get_scenic_data()